#!/usr/bin/python3
import sqlite3
import exifread
import os
import sys
import time

FILE_TABLE_NAME="files"
INDEX_TABLE_NAME="file_index"
REFREASH_TIME=5

def create_tbl(db_conn,drop_table=False):
    tbl_name=FILE_TABLE_NAME
    drop_tbl_sql="DROP TABLE IF EXISTS %s" %(tbl_name)
    create_tbl_sql=("CREATE TABLE IF NOT EXISTS %s( \r\n"
        "FILE_ID NUMBER KEY, \r\n"
        "RELPATH TEXT PRIMARY KEY, \r\n"
        "NAME TEXT, \r\n"
        "SUFFIX TEXT, \r\n"
        "FILE_SIZE TEXT, \r\n"
        "status TEXT default '', \r\n"
        "CTIME TIMESTAMP"
        ")" %(tbl_name) )
    #print(create_tbl_sql)
    cursor=db_conn.cursor()
    if drop_table:
        cursor.execute(drop_tbl_sql)
    cursor.execute(create_tbl_sql)
    cursor.close()
    
def create_index_tbl(db_conn,drop_table=False):
    tbl_name=INDEX_TABLE_NAME
    drop_tbl_sql="DROP TABLE IF EXISTS %s" %(tbl_name)
    create_tbl_sql=("CREATE TABLE IF NOT EXISTS %s( \r\n"
        "FILE_ID NUMBER PRIMARY KEY, \r\n"
        "IMAGE_MAKE TEXT, \r\n"
        "IMAGE_MODEL, \r\n"
        "IMAGE_DATETIME TIMESTAMP, \r\n"
        "Image_Width NUMBER, \r\n"
        "Image_Length NUMBER, \r\n"
        "GPSLatitudeRef TEXT, \r\n"
        "GPSLongitudeRef TEXT"
        ")" %(tbl_name) )    
    #print(create_tbl_sql)
    cursor=db_conn.cursor()
    if drop_table:
        cursor.execute(drop_tbl_sql)
    cursor.execute(create_tbl_sql)
    cursor.close()

def create_gps_tbl(db_conn,drop_table=False):
    tbl_name="gps_data"
    drop_tbl_sql="DROP TABLE IF EXISTS %s" %(tbl_name)
    create_tbl_sql=("CREATE TABLE IF NOT EXISTS %s( \r\n"
        "GPSLatitudeRef TEXT KEY, \r\n"
        "GPSLongitudeRef TEXT KEY, \r\n"
        "LOCATION TEXT"
        ")" %(tbl_name) )    
    #print(create_tbl_sql)
    cursor=db_conn.cursor()
    if drop_table:
        cursor.execute(drop_tbl_sql)
    cursor.execute(create_tbl_sql)
    cursor.close()
    
def getLatOrLng(tags, refKey, tudeKey):
    if refKey not in tags:
        return None
    ref=tags[refKey].printable
    LatOrLng=tags[tudeKey].printable[1:-1].replace(" ","").replace("/",",").split(",")
    LatOrLng=float(LatOrLng[0])+float(LatOrLng[1])/60+float(LatOrLng[2])/float(LatOrLng[3])/3600
    if refKey == 'GPS GPSLatitudeRef' and tags[refKey].printable != "N":
        LatOrLng=LatOrLng*(-1)
    if refKey == 'GPS GPSLongitudeRef' and tags[refKey].printable != "E":
        LatOrLng=LatOrLng*(-1)
    return LatOrLng

def get_location(lat,lng):
    url = 'http://api.map.baidu.com/reverse_geocoding/v3/?ak=%s&output=json&coordtype=wgs84ll&location=%s,%s' %('in3u3EdIaGYw7M6Kxz4g0yb25QhSBXve',str(lat),str(lng))
    req = urllib.request.urlopen(url)
    res = req.read().decode("utf-8") 
    str_value = json.loads(res)
    return str_value

def get_city(tags):
    lat = getLatOrLng(tags,'GPS GPSLatitudeRef','GPS GPSLatitude') #纬度
    lng = getLatOrLng(tags,'GPS GPSLongitudeRef','GPS GPSLongitude') #经度
    if not lat or not lng:
        return ""
    return get_location(lat,lng)


def insert_ignore_uniqe(db_conn,insert_sql,insert_data):
    if len(insert_data) > 0:
        cursor=db_conn.cursor()
        try:
            cursor.executemany(insert_sql, insert_data)
            db_conn.commit()
        except sqlite3.IntegrityError as e:
            if str(e).find("UNIQUE constraint") == -1:
                print(e)
                sys.exit(0)
            else:
                #print("SKIP UNIQUE constraint err")
                pass
        cursor.close()

def scan_dir(root_path,db_conn=None,max=-1,debug=False):
    all_data=[]
    alloced_id=0
    tbl_name=FILE_TABLE_NAME
    insert_sql="INSERT INTO %s (FILE_ID,RELPATH,NAME,SUFFIX,FILE_SIZE,CTIME) VALUES (?,?,?,?,?,?)" %(tbl_name)
    
    print("scan ...begin")
    begin_stamp=time.time()
    for root, dirs, files in os.walk(root_path):
        for name in files:
            alloced_id+=1
            one_data=[]
            
            full=os.path.join(root,name)
            suffix=os.path.splitext(name)[-1][1:].upper()
            relative_name=os.path.relpath(full, start=root_path)
            file_size=os.path.getsize(full)
            t = time.localtime(os.path.getctime(full))
            str_time= time.strftime("%Y-%m-%d %H:%M:%S", t)
            
            one_data.append(alloced_id);
            one_data.append(relative_name);
            one_data.append(name);
            one_data.append(suffix);
            one_data.append(file_size);
            one_data.append(str_time);
            
            if debug:
                print("\n====%s====" %(full))
                print(suffix)
                print(relative_name)
                print(file_size)
                print(str_time)
            all_data.append(tuple(one_data))
            
            end_stamp=time.time()
            if end_stamp - begin_stamp >= REFREASH_TIME:
                begin_stamp=end_stamp
                print("scan %d files" %(alloced_id))
            
            if len(all_data) >= 1000 :
                insert_ignore_uniqe(db_conn,insert_sql,all_data)
                all_data=[]

    insert_ignore_uniqe(db_conn,insert_sql,all_data)
    print("scan ...finish. totoal files = %d" %(alloced_id))

def get_tags(file):
    tags={}
    with open(file,'rb') as f:
        tags=exifread.process_file(f)
    return tags
    
def get_label(tags,label):
    if label not in tags.keys():
        return ""
    value=str(tags[label])
    try:
        dt=time.mktime(time.strptime(value,"%Y:%m:%d %H:%M:%S"))
        ret=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(dt))
    except:
        ret=value
    
    return ret

def process_file_info(root_path,db_conn):
    print("\nprocess ...begin")
    query_cursor=db_conn.cursor()
    update_cursor=db_conn.cursor()
    tbl_name=FILE_TABLE_NAME
    query_cursor.execute("select count(*) from %s " %(tbl_name))
    total_count=query_cursor.fetchone()[0]
    query_cursor.execute("select count(*) from %s where status<>'' " %(tbl_name))
    processed_count=query_cursor.fetchone()[0]
    querySql="select FILE_ID,RELPATH from %s where status=''" %(tbl_name)
    query_cursor.execute(querySql)    
    begin_stamp=time.time()
    for file_id,relative_name in query_cursor:
        one_data=[]
        full_path=os.path.join(root_path,relative_name)
        tags=get_tags(full_path)
        
        image_make=get_label(tags,"Image Make")
        image_model=get_label(tags,"Image Model")
        image_datetime=get_label(tags,"Image DateTime")
        image_width=get_label(tags,"EXIF ExifImageWidth")
        image_length=get_label(tags,"EXIF ExifImageLength")
        gps_latitude=get_label(tags,"GPS GPSLatitude")
        gps_longitude=get_label(tags,"GPS GPSLongitude")
        
        insert_sql="insert into %s values(?,?,?,?,?,?,?,?)" %(INDEX_TABLE_NAME)
        update_sql="update %s set status='index' where file_id=?" %(FILE_TABLE_NAME)
        
        update_cursor.execute(insert_sql,(file_id,image_make,image_model,image_datetime,image_width,image_length,gps_latitude,gps_longitude))
        update_cursor.execute(update_sql,(file_id,))
        db_conn.commit()

        processed_count+=1
        end_stamp=time.time()
        if end_stamp - begin_stamp >= REFREASH_TIME:
            begin_stamp=end_stamp
            print("process [%d/%d] files" %(processed_count,total_count))
    query_cursor.close()
    update_cursor.close()
    print("process ...finsh. [%d/%d]" %(processed_count,total_count))

if __name__ == '__main__':
    dbConn = sqlite3.connect("img_scan.db", check_same_thread=False)
    create_tbl(dbConn,False)
    create_index_tbl(dbConn,False)
    for dir in sys.argv[1:]:
        if os.path.exists(dir):
            scan_dir(dir,dbConn)
            process_file_info(dir,dbConn)
    dbConn.close()